#include <sqlite.hpp>
#include <iostream>
#include <string>
#include <vector>

struct Info {
  std::string name;
  int age;
  bool man;
};

sqlite::result queryInfo(sqlite::Sqlite &db, std::vector<Info> &vi) {
  vi.clear();
  auto sql = "SELECT * FROM test";
  auto stmt = db->prepareV2(sql);
  if (stmt->isOK()) {
    while (stmt->step() == SQLITE_ROW) {
      auto cols = stmt->columnCount();
      Info info;
      info.age = 0; info.man = 0;
      for (int i = 0; i < cols; ++i) {
        std::string colName = stmt->columnName(i);
        if (colName == "name") {
          info.name = (const char *)stmt->columnText(i);
        } else if (colName == "age") {
          info.age = stmt->columnInt(i);
        } else if (colName == "sex") {
          info.man = stmt->columnInt(i) == 1 ? true : false;
        }
      }
      if (!info.name.empty()) {
        vi.emplace_back(info);
      }
    }
    stmt->finalize();
  }

  return db->errcode();
}

sqlite::result insertInfo(sqlite::Sqlite &db, const Info &info) {
  sqlite::Stmt stmt;
  sqlite::result rc = SQLITE_OK;
  if (rc == SQLITE_OK) {
    auto sql = "INSERT INTO test(name,age,sex) VALUES(?,?,?)";
    stmt = db->prepareV2(sql);
    rc = db->errcode();
  }

  if (rc == SQLITE_OK)
    rc = stmt->bindText(info.name.c_str(), info.name.length());

  if (rc == SQLITE_OK)
    rc = stmt->bindInt(info.age);

  if (rc == SQLITE_OK)
    rc = stmt->bindInt(info.man ? 1 : 0);

  if (rc == SQLITE_OK)
    rc = stmt->step();

  if (rc == SQLITE_DONE)
    rc = SQLITE_OK;

  stmt->finalize();
  return rc;
}

sqlite::result deleteInfo(sqlite::Sqlite &db) {
  const char *sql = "DELETE FROM test WHERE name IN ('dzlua','kally','keke')";
  return db->exec(sql, nullptr, nullptr, nullptr);
}

sqlite::result checkTable(sqlite::Sqlite &db) {
  const char *sql = "CREATE TABLE IF NOT EXISTS test(id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL, age INTEGER NOT NULL, sex INTEGER NOT NULL)";
  return db->exec(sql, nullptr);
}

int main(int argc, char *argv[]) {
  auto db = sqlite::sqlite();
  auto rc = db->open("sqlite.db");
  if (!sqlite::isOK(rc)) {
    std::cout << "open error: " << sqlite::errstr(rc) << std::endl;
    return 0;
  }

  rc = checkTable(db);
  if (rc != SQLITE_OK) {
    std::cout << "exec error: " << sqlite::errstr(rc) << std::endl;
    db->close();
    return 0;
  }

  rc = deleteInfo(db);

  static Info infos[] = {
    { "dzlua", 18, true },
    { "kally", 18, false },
    { "keke", 1, true },
  };

  for (int i = 0; i < 3; ++i) {
    rc = insertInfo(db, infos[i]);
    if (rc != SQLITE_OK)
      std::cout << "insert error: " << sqlite::errstr(rc) << std::endl;
  }

  std::vector<Info> vi;
  rc = queryInfo(db, vi);
  if (rc == SQLITE_OK) {
    for (auto it = vi.begin(); it != vi.end(); ++it) {
      std::cout << "name: " << it->name
          << ", age: " << it->age
          << ", man: " << (it->man ? "true" : "false")
          << std::endl;
    }
  } else {
    std::cout << "query error: " << sqlite::errstr(rc) << std::endl;
  } 

  db->close();
  return 0;
}
